package com.ts.proj.service.impl;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.ts.common.constant.SQLConstant;
import com.ts.proj.entity.MeasureEntity;
import com.ts.proj.entity.excelEntity.MeasureCZJLVOExcel;
import com.ts.proj.entity.excelEntity.MeasureEntityExcel;
import com.ts.proj.entity.excelEntity.MonthEntityExcel;
import com.ts.proj.entity.excelEntity.ZCBEntityExcel;
import com.ts.proj.repository.MeasureRepository;
import com.ts.proj.service.MeasureService;

import com.ts.base.service.impl.CommonServiceImpl;

import com.ts.proj.vo.MeasureCZJLVO;
import com.ts.proj.vo.ReportVO;
import com.ts.proj.vo.StatisticsVO;
import com.ts.util.EntityAttributeUtil;
import com.ts.util.PageData;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


//说明 字符串：，作为替换字符串 LOWERMeasure                Measure

@Service
@Slf4j
public class MeasureServiceImpl extends CommonServiceImpl<MeasureEntity>  implements MeasureService {

    @Autowired
    private MeasureRepository measureRepository;


    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<MeasureEntity> findAll(PageData pageData, Integer start, Integer length) {
        Specification<MeasureEntity> sp = this.getSpecification(pageData,MeasureEntity.class);
        Sort sort = new Sort(Sort.Direction.DESC,"id");
        Pageable pageable = new PageRequest(start, length, sort);
        return measureRepository.findAll(sp,pageable).getContent();
    }

    @Override
    public long count(PageData pageData) {
        Specification<MeasureEntity> sp = this.getSpecification(pageData,MeasureEntity.class);
        return measureRepository.count(sp);
    }

    @Override
    public boolean deleteByIds(Iterable<MeasureEntity> iterable) {
        measureRepository.deleteInBatch(iterable);
        return false;
    }

    @Override
    public List<MeasureEntity> saveAll(List<MeasureEntity> entities) {
        return measureRepository.saveAll(entities);
    }

    @Override
    public List<MeasureEntity> findAllById(Integer[] ids) {
        List<MeasureEntity> list = measureRepository.findAllById(Arrays.asList(ids));
        if(list.size()==0){
            MeasureEntity entity = new MeasureEntity();
            list.add(entity);
        }
        return list;
    }

    @Override
    public boolean deleteAll() {
		measureRepository.deleteAll();
        return false;
    }

    @Override
    public List<PageData> sumByGroup(String param [],String field,String group) {
        StringBuilder sqlBuilder = new StringBuilder(" select SUM(t.tare) as tare ,SUM(t.gross_weight) as grossWeight ,SUM(t.suttle) as suttle ");
        sqlBuilder.append(field);
        sqlBuilder.append("  from ta_measure t  where t.delete_flag = 'N' and t.close_symbol=1 and t.return_symbol=0  ");
        sqlBuilder.append(group);
        Query dataQuery = entityManager.createNativeQuery(sqlBuilder.toString());
        List<PageData> list = new ArrayList<>();
        List resultList = dataQuery.getResultList();
        for(int i=0;i<resultList.size();i++){
            Object o[] = (Object[]) resultList.get(i);
            PageData tmp = new PageData();
            tmp.put("tare", o[0]);
            tmp.put("grossWeight", o[1]);
            tmp.put("suttle", o[2]);
            for(int j=0;j<param.length;j++){
                tmp.put(param[j], o[3 + j]);
            }
            list.add(tmp);
        }
        return list;
    }

    @Override
    public Specification getSpecification(PageData pageData, Class entityClass) {
        return new Specification() {
            public Predicate toPredicate(Root root, CriteriaQuery criteriaQuery, CriteriaBuilder criteriaBuilder) {
                    List<Predicate> preList = new ArrayList();
                List<String> fieldList = EntityAttributeUtil.entityAttribute(entityClass);

                for(int i = 0; i < fieldList.size(); ++i) {

                    if (null != pageData.get(fieldList.get(i))&&!fieldList.get(i).equals("measureId")&&!fieldList.get(i).equals("carId")
                            &&!fieldList.get(i).equals("grossFrom")&&!fieldList.get(i).equals("grossTo")
                            &&!fieldList.get(i).equals("materialName")&&!fieldList.get(i).equals("materialName")
                            &&!fieldList.get(i).equals("supplierName")&&!fieldList.get(i).equals("supplierName")
                            &&!fieldList.get(i).equals("customerName")&&!fieldList.get(i).equals("customerName")
                            &&!fieldList.get(i).equals("suttleDate")&&!fieldList.get(i).equals("suttleDate")
                            &&!fieldList.get(i).equals("tareFrom")&&!fieldList.get(i).equals("tareTo")) {
                        preList.add(criteriaBuilder.equal(root.get((String)fieldList.get(i)), pageData.get(fieldList.get(i)).toString()));
                    }else if(null != pageData.get(fieldList.get(i))&&fieldList.get(i).equals("measureId")){
                        preList.add(criteriaBuilder.like(root.get((String)fieldList.get(i)), "%"+pageData.get(fieldList.get(i)).toString()+"%"));
                    }else if(null != pageData.get(fieldList.get(i))&&fieldList.get(i).equals("carId")){
                        preList.add(criteriaBuilder.like(root.get((String)fieldList.get(i)), "%"+pageData.get(fieldList.get(i)).toString()+"%"));
                    }else if(null != pageData.get(fieldList.get(i))&&fieldList.get(i).equals("materialName")){
                        preList.add(criteriaBuilder.like(root.get((String)fieldList.get(i)), "%"+pageData.get(fieldList.get(i)).toString()+"%"));
                    }else if(null != pageData.get(fieldList.get(i))&&fieldList.get(i).equals("supplierName")){
                        preList.add(criteriaBuilder.like(root.get((String)fieldList.get(i)), "%"+pageData.get(fieldList.get(i)).toString()+"%"));
                    }else if(null != pageData.get(fieldList.get(i))&&fieldList.get(i).equals("customerName")){
                        preList.add(criteriaBuilder.like(root.get((String)fieldList.get(i)), "%"+pageData.get(fieldList.get(i)).toString()+"%"));
                    }
//                      else if(null != pageData.get(fieldList.get(i))&&fieldList.get(i).equals("suttleDate")){
//                        preList.add(criteriaBuilder.like(root.get((String)fieldList.get(i)), "%"+pageData.get(fieldList.get(i)).toString()+"%"));
//                    }
                }
                if(null != pageData.get("grossFrom")&&!"".equals(pageData.get("grossFrom").toString())){
                    preList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("grossWeightDate"),pageData.get("grossFrom").toString()));
                }
                if(null != pageData.get("grossTo")&&!"".equals(pageData.get("grossTo").toString())){
                    preList.add(criteriaBuilder.lessThanOrEqualTo(root.get("grossWeightDate"),pageData.get("grossTo").toString()));
                }
                if(null != pageData.get("tareFrom")&&!"".equals(pageData.get("tareFrom").toString())){
                    preList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("tareDate"),pageData.get("tareFrom").toString()));
                }
                if(null != pageData.get("tareTo")&&!"".equals(pageData.get("tareTo").toString())){
                    preList.add(criteriaBuilder.lessThanOrEqualTo(root.get("tareDate"),pageData.get("tareTo").toString()));
                }
                if(null != pageData.get("suttleDateFrom")&&!"".equals(pageData.get("suttleDateFrom").toString())){
                    preList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("suttleDate"),pageData.get("suttleDateFrom").toString()));
                }
                if(null != pageData.get("suttleDateTo")&&!"".equals(pageData.get("suttleDateTo").toString())){
                    preList.add(criteriaBuilder.lessThanOrEqualTo(root.get("suttleDate"),pageData.get("suttleDateTo").toString()));
                }


                Predicate[] p = new Predicate[preList.size()];
                return criteriaBuilder.and((Predicate[])preList.toArray(p));
            }
        };
    }

    @Override
    public List<PageData> reportInByMoveType(String customerName, String date,String month1,String mongth2) {
        return measureRepository.reportInByMoveType(customerName,date,month1,mongth2);
    }
    @Override
    public List<PageData> reportOutByMoveType(String customerName, String date,String month1,String mongth2) {
        return measureRepository.reportOutByMoveType(customerName,date,month1,mongth2);
    }

    @Override
    public List<PageData> reportInByMoveTypeYD(String customerName, String date,String month1,String mongth2,String toDate) {
        return measureRepository.reportInByMoveTypeYD(customerName,date,month1,mongth2,toDate);
    }
    @Override
    public List<PageData> reportOutByMoveType(String customerName, String date,String month1,String mongth2,String toDate) {
        return measureRepository.reportOutByMoveType(customerName,date,month1,mongth2,toDate);
    }

    @Override
    public List<MonthEntityExcel> month(String fac,String left,String right){
        String sql = SQLConstant.INMONTH;
        String s = sql.replaceAll("##CUSTOMERNAME##", fac).replaceAll("##FROMDATE##", left).replaceAll("##TODATE##", right);
        Query query = entityManager.createNativeQuery(s);
        List<Object> list = query.getResultList();
        List<MonthEntityExcel> result = new ArrayList<>();
        for(Object pd : list){
            Object[] obj = (Object[]) pd;
            MonthEntityExcel excel = new MonthEntityExcel();
            excel.setMn(objIsNull(obj[0]));
            excel.setSn(objIsNull(obj[1]));
            excel.setFactotal(objIsNull(obj[2]));
            excel.setMattotal(objIsNull(obj[3]));
            excel.setType(objIsNull(obj[4]));
            excel.setCattotal(objIsNull(obj[5]));
            result.add(excel);
        }
        return result;
    }

    @Override
    public List<MonthEntityExcel> outMonth(String fac, String left, String right) {
        String sql = SQLConstant.OUTMONTH;
        String s = sql.replaceAll("##CUSTOMERNAME##", fac).replaceAll("##FROMDATE##", left).replaceAll("##TODATE##", right);
        Query query = entityManager.createNativeQuery(s);
        List<Object> list = query.getResultList();
        List<MonthEntityExcel> result = new ArrayList<>();
        for(Object pd : list){
            Object[] obj = (Object[]) pd;
            MonthEntityExcel excel = new MonthEntityExcel();
            excel.setMn(objIsNull(obj[0]));
            excel.setSn(objIsNull(obj[1]));
            excel.setFactotal(objIsNull(obj[2]));
            excel.setMattotal(objIsNull(obj[3]));
            excel.setType(objIsNull(obj[4]));
            excel.setCattotal(objIsNull(obj[5]));
            result.add(excel);
        }
        return result;
    }

    @Override
    public List<ZCBEntityExcel> zcb(Integer page, Integer size, String sql) {
        String zcb = "select  car.car_id as carId ,car.car_model as carModel ,car.axes_number as axesNumber,car.transport_unit as transportUnit ,car.transportpermit,car.driver,car.certificate,car.care_weight as careWeight,substring_index(measure.material_name,'|',1) as material ,measure.measure_id as measureId,measure.gross_weight as gross ,measure.gross_weight_date as grossDate from " +
                " (" +
                " select DISTINCT c.car_id,c.car_model,c.axes_number,c.transport_unit,c.transportpermit,c.driver,c.certificate,c.care_weight from ta_car c " +
                " ) car," +
                " (" +
                " select t.material_name,t.measure_id,t.gross_weight,t.tare,t.suttle,t.gross_weight_date,t.tare_date,t.move_type,t.car_id from ta_measure t " +
                "  where t.move_type = 'OUT'  and t.delete_flag='N'  ) measure " +
                " where car.car_id = measure.car_id  and measure.gross_weight_date <> ''  ";
        if(!"".equals(sql)){
            zcb += " " + sql + " ";
        }
        zcb += " limit  " + page + " ," + size;
        Query query = entityManager.createNativeQuery(zcb);
        List<Object> list = query.getResultList();
        List<ZCBEntityExcel> result = new ArrayList<>();
        for(Object pd : list){
            Object[] obj = (Object[]) pd;
            ZCBEntityExcel excel = new ZCBEntityExcel();
            excel.setCarId(objIsNull(obj[0]));
            excel.setCarModel(objIsNull(obj[1]));
            excel.setAxesNumber(objIsNull(obj[2]));
            excel.setTransportUnit(objIsNull(obj[3]));
            excel.setTransportpermit(objIsNull(obj[4]));
            excel.setDriver(objIsNull(obj[5]));
            excel.setCertificate(objIsNull(obj[6]));
            excel.setCareWeight(objIsNull(obj[7]));
            excel.setMaterial(objIsNull(obj[8]));
            excel.setMeasureId(objIsNull(obj[9]));
            excel.setGross(objIsNull(obj[10]));
            excel.setGrossDate(objIsNull(obj[11]));
            result.add(excel);

        }
        return result;
    }

    @Override
    public List<MeasureCZJLVOExcel> czjl(Integer page, Integer size,String from,String to) {
        String sql = SQLConstant.totalReport;
        String ttt = sql.replaceAll("fromdate", from).replaceAll("todate",to);
        Query query = entityManager.createNativeQuery(ttt);
        List<Object> resultList = query.getResultList();
        List<MeasureCZJLVOExcel> result = new ArrayList<>();
        for(Object pd : resultList){
            Object[] obj = (Object[]) pd;
            MeasureCZJLVOExcel excel = new MeasureCZJLVOExcel();
            excel.setMaterial(objIsNull(obj[0]));
            excel.setSupplier(objIsNull(obj[1]));
//            excel.setGross(objIs0(obj[2]));
//            excel.setTare(objIs0(obj[3]));
            excel.setSuttle(objIs0(obj[4]));
//            excel.setTs(objIs0(obj[5]));
            excel.setCount(objIs0(obj[6]));
            result.add(excel);
        }
        return result;
    }

    @Override
    public Integer zcbCount(String sql) {
        Integer count = 0;
        String zcbCount = SQLConstant.zcbCount;
        if(!"".equals(sql)){
            zcbCount += " " + sql + " ";
        }
        Query query = entityManager.createNativeQuery(zcbCount);
        List<Object> resultList = query.getResultList();

        if(resultList.size()==1){
            try{
                BigInteger bCount = (BigInteger) resultList.get(0);
                Long lCount = bCount.longValue();
                count = Integer.valueOf(bCount.intValue());
            }catch (Exception e){
                count = 0;
            }
        }
        return count;
    }

    private String objIsNull(Object obj){
        if(null == obj){
            return "";
        }else{
            return obj.toString();
        }
    }
    private Double objIs0(Object obj){
        if(null == obj){
            return new Double(0);
        }else{
            return Double.valueOf(obj.toString());
        }
    }
}